const router = require('koa-router')()

router.get('/', async (ctx, next) => {
  await ctx.render('index', {
    title: 'Hello Koa 2!'
  })
})

router.get('/string', async (ctx, next) => {
  ctx.body = 'koa2 string'
})

router.get('/json', async (ctx, next) => {
  ctx.body = {
    title: 'koa2 json'
  }
})

router.get('/han', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="两汉" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/qing', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="清代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/weijin', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="魏晋" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/tang', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="唐代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/sui', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="隋代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/jindai', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="近代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/nbchao', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="南北朝" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/ming', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="明代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})



router.get('/song', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="宋代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/yuan', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="元代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/wudai', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="五代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/xianqin', async (ctx, next) => { 
  const sql = 'SELECT * FROM chinesepoetry WHERE dynasty="先秦" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/shan', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE name like"%山%" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/shui', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE name like"%水%" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/mei', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE name like"%梅%" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/lan', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE name like"%兰%" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/zhu', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE name like"%竹%" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/ju', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE name like"%菊%" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/cao', async (ctx, next) => { 
  const sql = 'SELECT * FROM chinesepoetry WHERE name like"%草%" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.get('/yan', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE name like"%烟%" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
  console.log(ctx.body)
})

router.get('/jiu', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE shangxi like"%酒%" and dynasty="唐代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})
router.get('/cha', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE shangxi like"%茶%" and dynasty="唐代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})
router.get('/chan', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE shangxi like"%禅%" and dynasty="唐代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})
router.get('/aiguo', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE shangxi like"%爱国%" and dynasty="唐代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})
router.get('/aiqing', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE shangxi like"%爱情%" and dynasty="唐代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})
router.get('/libie', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE shangxi like"%离别%" and dynasty="唐代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})
router.get('/sixiang', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE shangxi like"%思乡%" and dynasty="唐代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})
router.get('/gudu', async (ctx, next) => {
  const sql = 'SELECT * FROM chinesepoetry WHERE shangxi like"%孤独%" and dynasty="唐代" LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})


router.post('/save', async(ctx, next) => {
  // let index = ctx.request.body
  // console.log(index)
  let content = ctx.request.body.content;
  let name = ctx.request.body.name
  let openid = ctx.request.body.openid;
  let author = ctx.request.body.author;
  // console.log(index);
  console.log(openid);
  console.log(name);
  let sql = "INSERT INTO savepome (openid,content,name,author) VALUES(?,?,?,?)"
  let result = await ctx.db.EXCUTE(sql, [openid, content, name,author]);
  ctx.body = result
})

router.post('/delete', async(ctx, next) => {
  let name = ctx.request.body.name;
  console.log(name)
  let sql = "DELETE FROM savepome WHERE name = ?"
  let result = await ctx.db.EXCUTE(sql, [name]);
  ctx.body = {
    code: 200,
    "msg": "删除成功",
    result
  }
})

router.post('/show', async(ctx, next) => {
  const openid = ctx.request.body.openid
  console.log(openid)
  const sql = `SELECT * FROM savepome WHERE openid like "${openid}"`
  let result = await ctx.db.EXCUTE(sql);
  console.log(result);
  ctx.body = {
    result,
    code: 0
  }
  // console.log(ctx.body)
})

router.get('/famous', async(ctx, next) => {
  const sql = 'SELECT * FROM mingju LIMIT 100'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
  console.log(ctx.body)
})

router.post('/detail', async(ctx, next) => {
  const id = ctx.request.body.poetryId
  console.log(id)
  ctx.body = {
    code: 200,
    id: id
  }
})

router.get('/author', async(ctx, next) => {
  const sql = 'SELECT * FROM poet LIMIT 500'
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
  ctx.body = {
    result,
    code: 0
  }
  console.log(ctx.body)
})

router.post('/authorDetail', async(ctx, next) => {
  const sql = `SELECT * FROM chinesepoetry WHERE author like "${ctx.request.body.id}%"`
  let result = await ctx.db.EXCUTE(sql);
  console.log(result)
 ctx.body=result
})


router.post('/famdetail', async(ctx, next) => {
  const name = ctx.request.body.name
  console.log()
  ctx.body = {
    code: 200,
    name: name
  }
})

router.post('/showdetail', async(ctx, next) => {
  const id = ctx.request.body.id
  // console.log(JSON.parse(id))
  // const ide = id.split("'")[1].split("'")[0]
  console.log(id)
  const sql = `SELECT * FROM chinesepoetry WHERE poetryId like "${id}"`
  let result = await ctx.db.EXCUTE(sql);
  console.log(result);
  ctx.body = {
    result,
    code: 0
  }
  // console.log(ctx.body)
})

router.post('/showfamdetail', async(ctx, next) => {
  const name = ctx.request.body.name
  // console.log(JSON.parse(id))
  // const ide = id.split("'")[1].split("'")[0]
  console.log(name)
  const sql = `SELECT * FROM chinesepoetry WHERE name like "${name}"`
  let result = await ctx.db.EXCUTE(sql);
  console.log(result);
  ctx.body = {
    result,
    code: 0
  }
  // console.log(ctx.body)
})

router.post('/sreachbox', async(ctx, next) => {
  const name = ctx.request.body.name
  console.log(name)
  const sql = `SELECT * FROM chinesepoetry WHERE name like "${name}%" `
  const mysql = `SELECT * FROM poet WHERE name like "${name}%" `
  let result = await ctx.db.EXCUTE(sql);
  let res = await ctx.db.EXCUTE(mysql);
  console.log(res)
  ctx.body = {
    result,
    res,
    code: 0
  }
  // console.log(ctx.body)
})

router.post("/getMore", async (ctx, next) => {
  // console.log(ctx.request.body.dynasty.split('"')[1].split('"')[0]);
  let value = ctx.request.body.dynasty.split('"')[1].split('"')[0]
  const sql = `SELECT * FROM poet WHERE dynasty like "${value}" LIMIT 100`;
  let result = await ctx.db.EXCUTE(sql);
  ctx.body = result;
});

router.post('/history', async(ctx, next) => {
  // console.log(ctx.request.body)
  const openid = ctx.request.body.openid
  const history = ctx.request.body.name
  // console.log(openid, name)
  let sql = "INSERT IGNORE INTO history (openid,history) VALUES(?,?)"
  let result = await ctx.db.EXCUTE(sql, [openid, history]);
  ctx.body = result
  // ctx.body = "666"
})

router.get('/showhistory', async(ctx, next) => {
  const sql = 'SELECT * FROM history'
  let result = await ctx.db.EXCUTE(sql);
  // console.log(result)
  ctx.body = {
    result,
    code: 0
  }
})

router.post('/deletehistory', async(ctx, next) => {
  let openid = ctx.request.body.openid;
  // console.log(ctx.request.body.openid)
  let sql = "DELETE FROM history WHERE openid = ?"
  let result = await ctx.db.EXCUTE(sql, [openid]);
  ctx.body = {
    code: 200,
    "msg": "删除成功",
    result
  }
})

router.post('/historybox', async(ctx, next) => {
  const name = ctx.request.body.history
  console.log(name)
  const sql = `SELECT * FROM chinesepoetry WHERE name like "${name}%" `
  const mysql = `SELECT * FROM poet WHERE name like "${name}%" `
  let result = await ctx.db.EXCUTE(sql);
  let res = await ctx.db.EXCUTE(mysql);
  console.log(res)
  ctx.body = {
    result,
    res,
    code: 0
  }
  // console.log(ctx.body)
})

module.exports = router
